﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Text;
using Module.Common.Models;
using Module.Core.EntitiesInfo;
using Module.Core.EntitiesInfo.TablesInfo;

namespace Module.Core.DAO.DAO
{
    public class ProductsDao
    {
        #region Fields
        
        protected string connectionStringName;

        #endregion

        #region Methods
        
        /// <summary>
        /// Saves a record to the Singer table.
        /// </summary>
        public virtual int Insert(ProductsInfo productsInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ProductName", productsInfo.ProductName),
				new SqlParameter("@ZoneId", productsInfo.ZoneId),
				new SqlParameter("@CityId", productsInfo.CityId),
				new SqlParameter("@Price", productsInfo.Price),
				new SqlParameter("@Contents", productsInfo.Contents),
				new SqlParameter("@Description", productsInfo.Description),
                 new SqlParameter("@PageViews", productsInfo.PageViews),
				new SqlParameter("@CreateBy ", productsInfo.CreatedBy),
				new SqlParameter("@CreatedOn", productsInfo.CreatedOn),
				new SqlParameter("@Status", productsInfo.Status),
				new SqlParameter("@AvatarSmail", productsInfo.AvatarSmail),
				new SqlParameter("@AvatarBig", productsInfo.AvatarBig),
                new SqlParameter("@PriceVAT", productsInfo.Price_VAT),
                new SqlParameter("@Priority", productsInfo.Priority),
                new SqlParameter("@Keyword", productsInfo.Keyword),
                new SqlParameter("@CheckType", productsInfo.CheckType),
                new SqlParameter("@DealXu", productsInfo.DealXu),
                new SqlParameter("@EndDate", productsInfo.EndDate),
                new SqlParameter("@Description_Note", productsInfo.Description_Note),
                new SqlParameter("@Description_Hot", productsInfo.Description_Hot)
			};

            return (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Products_Insert", parameters);
        } 

        /// <summary>
        /// Updates a record in the Singer table.
        /// </summary>
        public virtual void Update(ProductsInfo productsInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                
                new SqlParameter("@ProductId", productsInfo.ProductId),
				new SqlParameter("@ProductName", productsInfo.ProductName),
				new SqlParameter("@ZoneId", productsInfo.ZoneId),
				new SqlParameter("@Price", productsInfo.Price),
				new SqlParameter("@Contents", productsInfo.Contents),
				new SqlParameter("@Description", productsInfo.Description),
             	new SqlParameter("@ModifiedOn", productsInfo.ModifiedOn),
                new SqlParameter("@ModifiedBy", productsInfo.ModifiedBy),
				new SqlParameter("@AvatarSmail", productsInfo.AvatarSmail),
				new SqlParameter("@AvatarBig", productsInfo.AvatarBig),
                new SqlParameter("@CheckType", productsInfo.CheckType),
                new SqlParameter("@Price_VAT", productsInfo.Price_VAT),
                new SqlParameter("@Priority", productsInfo.Priority),
                new SqlParameter("@Keyword", productsInfo.Keyword),
                new SqlParameter("@DealXu", productsInfo.DealXu),
                new SqlParameter("@EndDate", productsInfo.EndDate),
                new SqlParameter("@Description_Note", productsInfo.Description_Note),
                new SqlParameter("@Description_Hot", productsInfo.Description_Hot)
				
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "Products_Update", parameters);
        }

        /// <summary>
        /// Deletes a record from the Singer table by its primary key.
        /// </summary>
        public virtual void Delete(int productId)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ProductId", productId)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "Products_Delete", parameters);
        }

        // <summary>
       //  Selects a single record from the Singer table.
      //   </summary>
        public virtual ProductsInfo Select(int productsId)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@ProductId", productsId)
            };

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Products_Select", parameters))
            {
                if (dataReader.Read())
                {
                    return MakeProductInfo(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }
        public virtual ProductsInfo SelectFollowKeywors(string keyword, int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Keyword", keyword),
                new SqlParameter("@Status", status)
            };

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Products_SelectKeywords", parameters))
            {
                if (dataReader.Read())
                {
                    return MakeProductInfo(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }

        // <summary>
        //  Selects a single record from the Products table.
        //   </summary>
        public virtual DataTable SelectIndex(int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Status", status)
            };

            using (DataTable dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "ProductsIndex_Select", parameters))
            {
                return dt;
            }
        }

        public virtual void UpdateStatus(int producsId, int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ProductId", producsId),
                new SqlParameter("@Status", status),
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "Products_UpdateStatus", parameters);
        }


        public virtual ProductsInfo SelectCity(int cityId)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@CityId", cityId)
            };

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Products_GetCity", parameters))
            {
                if (dataReader.Read())
                {
                    return MakeProductInfo(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }
        /// <summary>
        /// Selects all records from the Singer table.
        /// </summary>
        public CHRTList<ProductsInfo> SelectAll(int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Status",status)
        };
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Products_SelectAll", parameters))
            {
                CHRTList<ProductsInfo> productsInfoList = new CHRTList<ProductsInfo>();
                while (dataReader.Read())
                {
                    ProductsInfo productsInfo = MakeProductInfo(dataReader);
                    productsInfoList.Add(productsInfo);
                }

                return productsInfoList;
            }
        }
        public CHRTList<ProductsInfo> SearchAll(string keyword, int status, int from, int to, int checkType, int zoneId, DateTime createdOnFrom, DateTime createdOnTo)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Keyword",keyword),
                                              new SqlParameter("@Status",status),
                                              new SqlParameter("@CheckType",checkType),
                                              new SqlParameter("@ZoneId",zoneId),
                                              new SqlParameter("@CreatedOnFrom",createdOnFrom),
                                              new SqlParameter("@CreatedOnTo ",createdOnTo),
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
        };
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Products_SearchAll", parameters))
            {
                CHRTList<ProductsInfo> productInfoList = new CHRTList<ProductsInfo>();
                while (dataReader.Read())
                {
                    ProductsInfo productInfo = MakeProductInfo(dataReader);
                    productInfoList.Add(productInfo);
                }

                return productInfoList;
            }
        }
        public int GetCount(string keyword, int status,int zoneId)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@Keyword", keyword),
                                                new SqlParameter("@Status", status),
                                                new SqlParameter("@ZoneId", zoneId)
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Products_GetCount", parameters);

        }

        public virtual int SelectCount(bool isTotalRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{           
				new SqlParameter("@isTotalRow", true.ToString())
			};

            return (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Sp_Products_Search", parameters);
        }

        public DataTable Get12Row(int BeginRow, int EndRow, int IsTotalRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@BeginRow", BeginRow),
                new SqlParameter("@EndRow", EndRow),
                new SqlParameter("@IsTotalRow", IsTotalRow)
                

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Sp_Products_Search", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }
        public DataTable GetDataLatest(int topN, int zoneId, int status, int checkType)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@TopN", topN),
                new SqlParameter("@ZoneID", zoneId),
                new SqlParameter("@Status", status),
                new SqlParameter("@CheckType",checkType)
                

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Products_Type_Latest", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }
        public DataTable GetTop3(int Status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Status", Status)                

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Products_Top3", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }

        public DataTable GetSanPhamCungNhom(int Status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Status", Status)                

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Products_SanPhamCungNhom", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }

        public DataTable GetCategory(int lang, int checkZone, int parentID, int moduleMenu, string keyOrderby)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Lang", lang)    ,
                new SqlParameter("@CheckZone", checkZone)  ,
                new SqlParameter("@ParentID", parentID)  ,
                new SqlParameter("@ModuleMenu", moduleMenu)  ,
                new SqlParameter("@KeyOrderby", keyOrderby)  

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "CMS_Zones_GetSite", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }

        public int GetPageview(string Keyword, int Status)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@Keyword", Keyword),
                                                new SqlParameter("@Status",Status)
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Products_SelectKeywords", parameters);

        }

        public DataSet GetProduct(int status, int currPage, int recodperPage, int pageSize)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {                                              
                                             
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperpage",recodperPage),
                                              new SqlParameter("@Pagesize",pageSize),
                                               new SqlParameter("@Status",status)

                                             
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "Products_PagingByLogID", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }

        public DataSet GetProductDanhMuc(int status, int currPage, int recodperPage, int pageSize, int zoneId)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {                                              
                                             
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperpage",recodperPage),
                                              new SqlParameter("@Pagesize",pageSize),
                                              new SqlParameter("@Status",status),
                                               new SqlParameter("@ZoneId",zoneId)
                                              
                                             
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "Products_SelectCategory", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }
        //Hiện thị danh sách sản phẩm theo danh mục
      public DataTable GetProductChiTietDanhMuc(int zoneId,int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ZoneId", zoneId),
                new SqlParameter("@Status", status) 

			};
            DataTable dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Product_LstDanhMucSanPham", parameters);

            if (dt != null && dt.Rows.Count > 0)
                return dt;
            return null;
        }
        /// <summary>
        /// Creates a new instance of the Singer class and populates it with data from the specified SqlDataReader.
        /// </summary>
        protected virtual ProductsInfo MakeProductInfo(SqlDataReader dataReader)
        {
            ProductsInfo productInfo = new ProductsInfo();
            productInfo.ProductId = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.PRODUCTID, 0);
            productInfo.ProductName = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.PRODUCTIDNAME, String.Empty);
            productInfo.ZoneId = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.ZONEID, 0);
            productInfo.CityId = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.CITYID,0);
            productInfo.Price = SqlClientUtility.GetFloat(dataReader, DbConstants.PRODUCTS.PRICE,0);
            productInfo.Contents = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.CONTENST, String.Empty);
            productInfo.Description= SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.DESCRIPTION, String.Empty);
            productInfo.PageViews = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.PAGEVIEWS,0);
            productInfo.CreatedBy = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.CREATEBY, String.Empty);
            productInfo.CreatedOn = SqlClientUtility.GetDateTime(dataReader, DbConstants.PRODUCTS.CREATEON, DateTime.Now);
            productInfo.Status = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.STATUS,0);
            productInfo.AvatarSmail = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.AVATARSMAIL, String.Empty);
            productInfo.AvatarBig = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.AVATARBIG, String.Empty);
            productInfo.CheckType = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.CHECKTYPE, 0);
            productInfo.ModifiedOn = SqlClientUtility.GetDateTime(dataReader, DbConstants.PRODUCTS.MODIFIEDON, DateTime.Now);
            productInfo.ModifiedBy = SqlClientUtility.GetString(dataReader,  DbConstants.PRODUCTS.M0DIFIEDBY, String.Empty);
            productInfo.Price_VAT = SqlClientUtility.GetFloat(dataReader,  DbConstants.PRODUCTS.PRICEVAT, 0);
            productInfo.Priority = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.PRIORITY, 0);
            productInfo.Keyword = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.KEYWORD, String.Empty);
            productInfo.DealXu = SqlClientUtility.GetInt32(dataReader, DbConstants.PRODUCTS.DEALXU, 0);
            productInfo.EndDate = SqlClientUtility.GetDateTime(dataReader, DbConstants.PRODUCTS.ENDDATE, DateTime.Now);
            productInfo.Description_Hot = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.DESCRIPTION_HOT, String.Empty);
            productInfo.Description_Note = SqlClientUtility.GetString(dataReader, DbConstants.PRODUCTS.DESCRIPTION_NOTE, String.Empty);
            return productInfo;
        }

        #endregion

        #region fronend
        #endregion
    }
}
